﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using BPM.ReportDesigner.AjaxPage;

namespace BPM.ReportDesigner.GenerateReportFiles
{
    public partial class Template_20130918103822 : System.Web.UI.Page
    {
        private string UserName = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            UserName = new GetUserInfo().tttt();
            if (!IsPostBack)
            {
                BingData();
            }
        }

        public void BingData()
        {
             int PageIndex = AspNetPager1.CurrentPageIndex - 1; 
int PageSize = AspNetPager1.PageSize; 
string strWhere = ""; 
if (DOCUMENTNO.Text.Trim() != "") 
{ 
    strWhere += " AND DOCUMENTNO LIKE '%" + DOCUMENTNO.Text.Trim() + "%' "; 
} 
if (PROCESSSUMMARY.Text.Trim() != "") 
{ 
    strWhere += " AND PROCESSSUMMARY = '" + PROCESSSUMMARY.Text.Trim() + "' "; 
} 
 
AspNetPager1.RecordCount = PageCountQuery(strWhere); 
string sql="select * from (select  row_number() over(order by PROC_INVOICE.DOCUMENTNO ASC) as rowid,PROC_INVOICE.FORMID AS 'FORMID',PROC_INVOICE.PROCESSNAME AS 'PROCESSNAME',PROC_INVOICE.INCIDENT AS 'INCIDENT',PROC_INVOICE.DOCUMENTNO AS 'DOCUMENTNO1',PROC_INVOICE.APPLICANT AS 'APPLICANT',PROC_INVOICE.REQUESTDATE AS 'REQUESTDATE',PROC_INVOICE.DEPARTMENT AS 'DEPARTMENT',PROC_INVOICE.COMPANY AS 'COMPANY',PROC_INVOICE.PROCESSSUMMARY AS 'PROCESSSUMMARY',PROC_INVOICE.STATUS AS 'STATUS',PROC_INVOICE.KPCOMPANY AS 'KPCOMPANY',PROC_INVOICE.FKCOMPANY AS 'FKCOMPANY',PROC_INVOICE.INVOICEAMOUNT AS 'INVOICEAMOUNT',PROC_INVOICE.INVOICETYPE AS 'INVOICETYPE',PROC_INVOICE.RATE AS 'RATE',PROC_INVOICE.INVOICESUMMARY AS 'INVOICESUMMARY',PROC_INVOICE.REMARK AS 'REMARK',PROC_INVOICE.INVOICENO AS 'INVOICENO',PROC_INVOICE.KPAMOUNT AS 'KPAMOUNT' FROM PROC_INVOICE where 1=1  "+strWhere+" ) a WHERE (a.rowid > ("+AspNetPager1.CurrentPageIndex+"-1) *10 and a.rowid <= "+AspNetPager1.CurrentPageIndex+"*10)"; 
DataSet ds = ExecuteNonQuery(sql); 
ReportList.DataSource = ds; 
ReportList.DataBind(); 

        }

        protected void AspNetPager1_PageChanged(object sender, EventArgs e) 
{ 
  BingData(); 
} 

public int PageCountQuery(string strWhere) 
{ 
DataSet ds = ExecuteNonQuery("SELECT count(*) FROM PROC_INVOICE WHERE 1=1  "+strWhere+" "); 
return int.Parse(ds.Tables[0].Rows[0][0].ToString()); 
} 


        public void SearchButton_Click(object sender, EventArgs e) 
{ 
     BingData(); 
} 


        protected void ExportToExcel_Click(object sender, EventArgs e)
        {
            string strWhere = ""; 
if (DOCUMENTNO.Text.Trim() != "") 
{ 
    strWhere += " AND DOCUMENTNO LIKE '%" + DOCUMENTNO.Text.Trim() + "%' "; 
} 
if (PROCESSSUMMARY.Text.Trim() != "") 
{ 
    strWhere += " AND PROCESSSUMMARY = '" + PROCESSSUMMARY.Text.Trim() + "' "; 
} 
 
DataTable tb = ExecuteNonQuery("SELECT PROC_INVOICE.FORMID AS 'FORMID',PROC_INVOICE.PROCESSNAME AS 'PROCESSNAME',PROC_INVOICE.INCIDENT AS 'INCIDENT',PROC_INVOICE.DOCUMENTNO AS 'DOCUMENTNO1',PROC_INVOICE.APPLICANT AS 'APPLICANT',PROC_INVOICE.REQUESTDATE AS 'REQUESTDATE',PROC_INVOICE.DEPARTMENT AS 'DEPARTMENT',PROC_INVOICE.COMPANY AS 'COMPANY',PROC_INVOICE.PROCESSSUMMARY AS 'PROCESSSUMMARY',PROC_INVOICE.STATUS AS 'STATUS',PROC_INVOICE.KPCOMPANY AS 'KPCOMPANY',PROC_INVOICE.FKCOMPANY AS 'FKCOMPANY',PROC_INVOICE.INVOICEAMOUNT AS 'INVOICEAMOUNT',PROC_INVOICE.INVOICETYPE AS 'INVOICETYPE',PROC_INVOICE.RATE AS 'RATE',PROC_INVOICE.INVOICESUMMARY AS 'INVOICESUMMARY',PROC_INVOICE.REMARK AS 'REMARK',PROC_INVOICE.INVOICENO AS 'INVOICENO',PROC_INVOICE.KPAMOUNT AS 'KPAMOUNT' FROM PROC_INVOICE WHERE 1=1  "+strWhere+"  order by PROC_INVOICE.DOCUMENTNO ASC").Tables[0]; 
Response.Clear(); 
Response.Charset = "GB2312"; 
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 
Response.ContentType = "application/ms-excel"; 
Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(DateTime.Now.ToShortDateString(), System.Text.Encoding.GetEncoding("GB2312")) + ".xls"); 
CultureInfo cult = new CultureInfo("zh-CN", true); 
StringWriter sw = new StringWriter(cult); 
HtmlTextWriter htw = new HtmlTextWriter(sw); 
DataGrid dgrid = new DataGrid(); 
dgrid.DataSource = tb.DefaultView; 
dgrid.AllowPaging = false; 
dgrid.DataBind(); 
htw.WriteLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=GB2312\">"); 
dgrid.RenderControl(htw); 
Response.Write(sw.ToString()); 
Response.End(); 

        }

        public DataSet ExecuteNonQuery(string sql) 
{ 
     using(OleDbConnection oleConn=new OleDbConnection("Provider=SQLOLEDB.1;User ID=sa;Password=sa;Initial Catalog=UltimusBusiness;Data Source=.")) 
     { 
         using (OleDbCommand cmd = new OleDbCommand(sql, oleConn)) 
         { 
             using (OleDbDataAdapter oda = new OleDbDataAdapter(cmd)) 
             { 
                 DataSet ds = new DataSet(); 
                 oda.Fill(ds); 
                 return ds; 
             } 
         } 
     } 
} 


    }
}